/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.xmlProject.ultils;

import com.xmlProject.DTO.ProductDTO;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLXML;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import javax.xml.transform.stream.StreamSource;
import sample.accounts.Product;

/**
 *
 * @author Admin
 */
public class DBUltils {

    private static Connection makeConnection() {
        try {
            Context ctx = new InitialContext();
            Context envCtx = (Context) ctx.lookup("java:comp/env");
            DataSource ds = (DataSource) envCtx.lookup("DBCon");
            Connection con = ds.getConnection();
            return con;
        } catch (SQLException ex) {
            Logger.getLogger(DBUltils.class.getName()).log(Level.SEVERE, null, ex);
        } catch (NamingException ex) {
            Logger.getLogger(DBUltils.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public static String checkLogin(String username, String password) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = makeConnection();
            String user = "detailedInfo.value('(/account/username)[1]','nvarchar(50)') AS username";
            String pass = "detailedInfo.value('(/account/password)[1]','nvarchar(50)') AS password";
            String sql = "SELECT " + user + "," + pass + " FROM TBL_UserInfo WHERE username=? AND password=?";
            stm = con.prepareStatement(sql);
            stm.setString(1, username);
            stm.setString(2, password);
            rs = stm.executeQuery();
            if (rs.next()) {
                String result = "";
                if (rs.getString(2).isEmpty()) {
                    result = rs.getString(1) + " " + rs.getString(3);
                }
                result = rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3);
                return result;
            }
        } catch (SQLException ex) {
            System.out.println("Execption occurs: " + ex.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                System.out.println("Execption occurs: " + e.getMessage());
            }
        }
        return null;
    }

    public static ProductDTO[] getProductInfos() {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        List<ProductDTO> list = new ArrayList<ProductDTO>();
        try {
            con = makeConnection();
            String productName = "productInfo.value('(/product/productName)[1]','nvarchar(50)')";
            String category = "productInfo.value('(/product/category)[1]','nvarchar(50)')";
            String manufacturer = "productInfo.value('(/product/manufacturer)[1]','nvarchar(50)')";
            String description = "productInfo.value('(/product/description)[1]','nvarchar(50)')";
            String price = "productInfo.value('(/product/price)[1]','nvarchar(50)')";
            String sql = "SELECT id," + productName + "," + category + "," + manufacturer + "," + description + "," + price + " FROM TBL_Product";
            stm = con.prepareStatement(sql);
            rs = stm.executeQuery();
            while (rs.next()) {
                int id = rs.getInt(1);
                String proName = rs.getString(2);
                String cat = rs.getString(2);
                String manu = rs.getString(3);
                String desc = rs.getString(4);
                float p = Float.parseFloat(rs.getString(6));
                Product pro = new Product();
                pro.setId(BigInteger.valueOf(id));
                pro.setProductName(proName);
                pro.setCategory(cat);
                pro.setManufacturer(manu);
                pro.setDescription(desc);
                pro.setPrice(p);
                ProductDTO product = new ProductDTO(pro);
                list.add(product);
                pro = null;
            }
            ProductDTO[] products = new ProductDTO[list.size()];
            list.toArray(products);
            return products;
        } catch (SQLException ex) {
            System.out.println("Execption occurs: " + ex.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                System.out.println("Execption occurs: " + e.getMessage());
            }
        }
        return null;
    }

    public static StreamSource getProductInfoByID(int id) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = makeConnection();
            String sql = "SELECT productInfo FROM TBL_Product WHERE id = ?";
            stm = con.prepareStatement(sql);
            stm.setInt(1, id);
            SQLXML sqlxml = null;
            rs = stm.executeQuery();
            if (rs.next()) {
                sqlxml = rs.getSQLXML(1);
                StreamSource sc = sqlxml.getSource(null);
                sqlxml.free();
                return sc;
            }
        } catch (SQLException ex) {
            System.out.println("Execption occurs: " + ex.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                System.out.println("Execption occurs: " + e.getMessage());
            }
        }
        return null;
    }
}
